﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
/// <summary>
/// Summary description for AccessData
/// </summary>
public class AccessData
{
    
    private string ConnectString = ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString;
    private string error="";
    public SqlConnection GetConnect()
    {
        return new SqlConnection(ConnectString);
    }
    //insert use store
    public void ExecSProcedure(string Sprocedure_name, params Object[] ob)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = Sprocedure_name;
       SqlConnection cnn = GetConnect();
        cnn.Open();
        cmd.Connection = cnn;
        SqlCommandBuilder.DeriveParameters(cmd);
        for (int i = 1; i < cmd.Parameters.Count; ++i)
            cmd.Parameters[i].Value = ob[i - 1];
        cmd.ExecuteNonQuery();
        cnn.Close();
        cmd.Dispose();
        cnn.Dispose();
    }

    public DataTable getTableSProcedure(string Sprocedure_name, params Object[] ob)
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = Sprocedure_name;
        SqlConnection cnn = GetConnect();
        cnn.Open();
        cmd.Connection = cnn;
        SqlCommandBuilder.DeriveParameters(cmd);
        for (int i = 1; i < cmd.Parameters.Count; ++i)
            cmd.Parameters[i].Value = ob[i - 1];
        DataTable dt;
        try
        {
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            dt = new DataTable();
            da.Fill(dt);
            da.Dispose();
            cnn.Close();
        }
        catch (SqlException ex)
        {
            string error = ex.ToString();
            return null;
        }
        cmd.Dispose();
        cnn.Close();
        cnn.Dispose();
        return dt;
    }

     
    public int getValueProcedure(string storedProcedureName,string valuestring)
    {
        SqlConnection connection = GetConnect();
        connection.Open();

        // Command - specify as StoredProcedure
        SqlCommand command = new SqlCommand(storedProcedureName, connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@username", SqlDbType.VarChar).Value = valuestring;
        // Return value as parameter
        SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
        returnValue.Direction = ParameterDirection.ReturnValue;
        command.Parameters.Add(returnValue);

        // Execute the stored procedure
        
        command.ExecuteNonQuery();
        connection.Close();

        return Convert.ToInt32(returnValue.Value);
    }
}